<?php

//TODO: make these variables command-line arguments

$server   = 'localhost';
$port     = '3306';
$dbname   = 'DBNAME';
$user     = 'root';
$password = '';

// Connecting, selecting database
$link = mysql_connect($server . ':' . $port, $user, $password) or die('Could not connect: ' . mysql_error());
mysql_select_db($dbname) or die('Could not select database');
echo "Connected OK\n";

 while (true) {
 	//turn error reporting on
// 	ini_set('display_errors',1);
// 	error_reporting(E_ALL|E_STRICT);

 	// add query history if it exits
 	readline_read_history ("query_history.txt");
 	
	$query = readline("SQL Console> ");
	
 	if (strcmp($query, "exit")==0 || strcmp($query, "quit")==0 || strcmp($query, "exit;")==0 || strcmp($query, "quit;")==0) {
		echo "Goodbye!\n";
		break;
	}
	
	while(substr($query, -1) != ";"){
		$query .= " " . readline("           > ");
	}
	
	if(substr($query, -1) === ";"){
		$query = substr($query, 0, -1);
	}
	
	// add to history
	readline_add_history($query.";");
	
	echo "Executing: " . $query . "\n";
	//$time_start = microtime_float();

	// run the query
	$result = mysql_query($query);

	if (!$result) {
		echo "Query failed: " . mysql_error() . " error number: " . mysql_errno() . " \n" ;
	}
	elseif (stristr($query, 'select') == TRUE  || stristr($query, 'show') == TRUE) {
		$num_rows = mysql_num_rows($result);

		if ($num_rows == 0) {
			echo "Empty set\n";
		}
		else{
			$num_columns = mysql_num_fields ($result);
			$num_rows = mysql_num_rows($result);
			for($i=0; $i < $num_columns; $i++){
				$max_col_len[$i] = null;
			}

			// index the max width a column has to be
			while ($line = mysql_fetch_row($result)){
				$tmp = mysql_fetch_lengths($result);
				for ($i=0; $i < $num_columns; $i++) {
					if($tmp[$i] > $max_col_len[$i]){
						$max_col_len[$i] = $tmp[$i];
					}
				}
			}

			// reset the internal pointer of the mysql query back to zero
			mysql_data_seek( $result, 0 );

			// output the column names as well as an outline for the console
			if ($num_rows != 0) {
				$outline = "+";
				$wht_spc = array();
				$col_array = array();
					
				for ($i=0; $i < $num_columns; $i++) {
					$outline .= "-";
					$col_array[$i] = mysql_field_name ($result ,$i);
					$wht_spc[$i] = "";

					// compare column length with max length, if column length is bigger then adds to index
					$col_name_length = strlen($col_array[$i]);
					if($col_name_length > $max_col_len[$i]){
						$max_col_len[$i] = $col_name_length;
					}

					// sets the outline to make the format look nice
					for ($j=0; $j < $max_col_len[$i] ; $j++) {
						$outline .= "-";
					}

					// set the spacing to make the format look nice
					for ($k=0; $k < $max_col_len[$i] - $col_name_length; $k++) {
						$wht_spc[$i] .= " ";
					}

					$outline .= "-+";
				}
				echo $outline . "\n";
					
				// outputs the column headers
				for ($i=0; $i < $num_columns; $i++) {
					echo "| ". $col_array[$i] . $wht_spc[$i] . " ";
				}
					
				echo "|\n";
				echo $outline . "\n";
					
				// resets the arrays
				unset($wht_spc);
				unset($col_array);
			}

			// show the results
			while ($row = mysql_fetch_row($result)) {
				for ($i=0; $i < $num_columns ; $i++) {
					$wht_spc = "";

					// set the spacing to make the format look nice
					for ($k=0; $k < $max_col_len[$i] - strlen($row[$i]); $k++) {
						$wht_spc .= " ";
					}
					echo "| ". $row[$i] . $wht_spc ." ";
				}
				echo "|\n";
			}
			echo $outline . "\n";

				
			//print out rows returned
			if ($num_rows == 1) {
				echo $num_rows . " row in set\n";
			} else {
				echo $num_rows . " rows in set\n";
			}

			//frees result from memory
			mysql_free_result($result);
		}
		
	}
	else{
		//print out rows affected
		$num_rows = mysql_affected_rows();
		if ($num_rows == 1) {
			echo $num_rows . " row affected\n";
		} else {
		    echo $num_rows . " rows affected\n";
		}
	}
	//$time_end = microtime_float();

	//echo "Query took " . ($time_start - $time_end) . " second(s).\n";

	// writes query history to file
	readline_write_history ("query_history.txt");
	
	// clears the history so duplicate queries won't be written to file
	readline_clear_history ();
}

echo "Closing connections...\n";
mysql_close($link);
?>

